<?php
$servername = "localhost:3306";
$username = "root";
$password = "";

//Create Connection
$conn = new mysqli($servername, $username, $password);

//Check connection
if($conn->connect_error){
	die("Connection failed: " . $conn->conect_error);
}


//Create Database and tables schema. Queries are from real_estate.sql This code has to be executed only once

//$insert_data = mysqli_real_escape_string();
// $passHash = "$2y$10$c9TipNQw5vYP9vAyKtyaUOSq5yL4mkvKzZME7tKNBPe/30w8PJNCu";
$sql = '
	CREATE DATABASE IF NOT EXISTS real_estate_dbb;
	USE real_estate_dbb;

	CREATE TABLE IF NOT EXISTS users(
    	id INT(10) AUTO_INCREMENT PRIMARY KEY,
    	login VARCHAR(20) UNIQUE NOT NULL,
    	type VARCHAR(10) NOT NULL DEFAULT "customer",
    	pass VARCHAR(255) NOT NULL
	);
	
	CREATE TABLE IF NOT EXISTS properties(
    	id INT(10) AUTO_INCREMENT PRIMARY KEY,
    	user_id INT(10) NOT NULL,
    	bedrooms INT(10) NOT NULL,
    	price INT(10) NOT NULL,
    	description VARCHAR(1500) NOT NULL DEFAULT "N/A",
    	date_listed TIMESTAMP DEFAULT NOW(),
    	FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
	);

	CREATE TABLE IF NOT EXISTS addresses(
    	property_id INT(10) PRIMARY KEY,
    	city VARCHAR(40) NOT NULL,
    	suburb VARCHAR(40) NOT NULL,
    	street_name VARCHAR(40) NOT NULL,
    	street_number INT(10) NOT NULL,
    	FOREIGN KEY(property_id) REFERENCES properties(id) ON DELETE CASCADE
	);

	CREATE TABLE IF NOT EXISTS photos(
    	id INT(10) AUTO_INCREMENT PRIMARY KEY,
    	property_id INT(10) NOT NULL,
    	photo_url VARCHAR(255) NOT NULL UNIQUE,
    	FOREIGN KEY(property_id) REFERENCES properties(id) ON DELETE CASCADE
	);

	CREATE TABLE IF NOT EXISTS wishlists(
    	property_id INT(10),
    	user_id INT(10),
		state INT(1),
    	FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
    	FOREIGN KEY(property_id) REFERENCES properties(id) ON DELETE CASCADE,
    	PRIMARY KEY(property_id, user_id)
	);
	CREATE TABLE IF NOT EXISTS featuredlists(
		property_id INT(10),
		FOREIGN KEY(property_id) REFERENCES properties(id) ON DELETE CASCADE,
		PRIMARY KEY(property_id)
	);
	
-- This adds default admin user with pass: admin
-- and should be changed later from admin page.
	 	INSERT  INTO users(login, type, pass)
	 	VALUES("admin", "admin", "$2y$10$c9TipNQw5vYP9vAyKtyaUOSq5yL4mkvKzZME7tKNBPe/30w8PJNCu"),
		("Vacya", "customer", "$2y$10$irPjoT0HN8AwFlCvCu48deHJzISC3XqZlloeVvxpCVjNz5e7Czdfu"),
	    ("Sharik", "customer", "$2y$10$fEnTGFgatpw/uaKz2x9J8OpLjqXRdp3Og7ng6iUt8tm4g15IpDvAa"),
	    ("Oleg", "customer", "$2y$10$c9TipNQw5vYP9vAyKtyaUOSq5yL4mkvKzZME7tKNBPe/30w8PJNCu")
	 	ON DUPLICATE KEY UPDATE id = id;
			
			
';
		

if($conn->multi_query($sql) == FALSE){
	echo "Error creating DB!<br>";
}

